package app;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

import command_impl.LoadCommand;
import pojo.Account;
import pojo.Catalog;
import pojo.Client;
import pojo.ClientStatus;
import pojo.ClientType;
import pojo.Order;
import pojo.OrderItem;
import pojo.OrderStatus;
import pojo.Payment;
import pojo.PaymentMethod;
import pojo.PaymentStatus;
import pojo.Product;
import pojo.ProductUnit;
import pojo.TransacationType;
import pojo.Transaction;
import pojo.User;
import util.OrderConsole;

public class OrderDao {

	public Connection getConn() throws SQLException {
		Connection conn = null;
		String url ="jdbc:mysql://localhost:3306/order_manage?useSSL=false";
		String username = "qingke_user";
		String password = "qingke";
		conn = DriverManager.getConnection(url, username, password);
		return conn;
	}

	public void loadDriver() throws ClassNotFoundException {
		Class.forName("com.mysql.jdbc.Driver");
	}

	public void close(Connection conn, ResultSet rs, PreparedStatement pStatement, CallableStatement cs) {
		try {
			if (rs != null)
				rs.close();
			if (cs != null)
				cs.close();
			if (pStatement != null)
				pStatement.close();
			if (conn != null)
				conn.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public User signupUser(User user) {

		Connection conn = null;
		CallableStatement cs = null;
		ResultSet rs = null;
		String username = user.getUsername();
		String password = user.getPassword();
		String email = user.getEmail();
		String phone = user.getPhone();
		String name = user.getName();
		Client client = user.getClient();

		try {
			loadDriver();
			conn = getConn();
			String sql = "call insert_user(?,?,?,?,?)";
			cs = conn.prepareCall(sql);
			cs.setString(1, username);
			cs.setString(2, password);
			cs.setString(3, email);
			cs.setString(4, phone);
			cs.setString(5, name);
			rs = cs.executeQuery();

			while (rs.next()) {
				int id = rs.getInt("id");
				user = new User(id, username, password, email, phone, name, client);
				return user;
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
				cs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}

	public User login(String username, String password) {
		Connection conn = null;
		CallableStatement cs = null;
		ResultSet rs = null;

		try {
			loadDriver();
			conn = getConn();
			String sql = "call order_manage.user_login(?,?)";
			cs = conn.prepareCall(sql);
			cs.setString(1, username);
			cs.setString(2, password);
			rs = cs.executeQuery();

			while (rs.next()) {
				int id = rs.getInt("id");
				String email = rs.getString("email");
				String phone = rs.getString("phone");
				String name = rs.getString("name");
				Client client = getClient("id", String.valueOf(rs.getInt("client_id")));
				User us = new User(id, username, password, email, phone, name, client);
				OrderApp.getInstance().setUser(us);
				new LoadCommand(us).execute();
				return us;
			}

		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
				cs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}

	// public void loadClient(User user){
	// Client client = user.getClient();
	// int id = client.getId();
	// Connection conn = null;
	// CallableStatement cs = null;
	// ResultSet rs = null;
	//
	// try {
	// loadDriver();
	// conn = getConn();
	// String sql = "call order_manage.get_client_from_id(?)";
	// cs = conn.prepareCall(sql);
	// cs.setInt(1, client.getId());
	// rs=cs.executeQuery();
	//
	// while(rs.next()){
	//
	// }
	//
	// } catch (ClassNotFoundException e) {
	// e.printStackTrace();
	// } catch (SQLException e) {
	// e.printStackTrace();
	// }
	//
	// }

	public User getUser(String mode, String pattern) {
		Connection conn = null;
		CallableStatement cs = null;
		ResultSet rs = null;

		try {
			loadDriver();
			conn = getConn();
			if ("id".equals(mode)) {
				String sql = "call order_manage.get_user_from_id(?)";
				cs = conn.prepareCall(sql);
				cs.setInt(1, Integer.parseInt(pattern));
			} else {
				String sql = "call order_manage.get_user_from_username(?)";
				cs = conn.prepareCall(sql);
				cs.setString(1, pattern);
			}
			rs = cs.executeQuery();

			while (rs.next()) {
				int id = rs.getInt("id");
				String username = rs.getString("username");
				String password = rs.getString("password");
				String email = rs.getString("email");
				String phone = rs.getString("phone");
				String name = rs.getString("name");
				Client client = getClient("id", String.valueOf(rs.getInt("client_id")));
				User user = new User(id, username, password, email, phone, name, client);
				return user;
			}

		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
				cs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}

	public Client getClient(String mode, String pattern) {
		Connection conn = null;
		CallableStatement cs = null;
		ResultSet rs = null;
		try {
			loadDriver();
			conn = getConn();
			if ("id".equals(mode)) {
				String sql = "call order_manage.get_client_from_id(?)";
				cs = conn.prepareCall(sql);
				cs.setInt(1, Integer.parseInt(pattern));
			} else {
				String sql = "call order_manage.get_client_from_name(?)";
				cs = conn.prepareCall(sql);
				cs.setString(1, pattern);
			}

			rs = cs.executeQuery();
			if (rs == null) {
				return null;
			}

			while (rs.next()) {
				int id = rs.getInt("id");
				String name = rs.getString("name");
				String phone = rs.getString("phone");
				ClientType cType = getClientType(rs.getInt("client_type_id"));
				ClientStatus cStatus = getClientStatus(rs.getInt("client_status_id"));
				Account account = getAccount(rs.getInt("account_id"));

				Client client = new Client(id, name, phone, cType, cStatus, account);
				return client;
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
				cs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}

	public Account getAccount(int id) {
		Account account = null;
		Connection conn = null;
		CallableStatement cs = null;
		ResultSet rs = null;

		try {
			loadDriver();
			conn = getConn();
			String sql = "call order_manage.get_account(?)";
			cs = conn.prepareCall(sql);
			cs.setInt(1, id);
			rs = cs.executeQuery();
			while (rs.next()) {
				double balance = rs.getDouble("balance");
				account = new Account(id, balance);
				return account;
			}

		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
				cs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return null;
	}

	public Client signupClient(Client client) {
		if (getClient("id", String.valueOf(client.getId())) != null) {
			OrderConsole.println("client已存在");
			return null;
		}

		Connection conn = null;
		CallableStatement cs = null;
		ResultSet rs = null;

		try {
			loadDriver();
			conn = getConn();
			String sql = "call order_manage.insert_client(?,?,?,?)";
			cs = conn.prepareCall(sql);
			cs.setString(1, client.getName());
			cs.setString(2, client.getPhone());
			cs.setInt(3, getClientTypeId(client.getType()));
			cs.setInt(4, getClientStatusId(client.getStatus()));
			rs = cs.executeQuery();

			while (rs.next()) {
				int id = rs.getInt("id");
				client.setId(id);
				return client;
			}
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
				cs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}

	public ClientType getClientType(int id) {
		Connection conn = null;
		CallableStatement cs = null;
		ResultSet rs = null;
		try {
			loadDriver();
			conn = getConn();
			String sql = "call order_manage.get_client_type(?)";
			cs = conn.prepareCall(sql);
			cs.setInt(1, id);
			rs = cs.executeQuery();
			if (rs == null) {
				return null;
			}
			while (rs.next()) {
				String type = rs.getString("name");
				ClientType cType = ClientType.valueOf(type.toUpperCase());
				return cType;
			}

		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
				cs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}

	public ClientStatus getClientStatus(int id) {
		Connection conn = null;
		CallableStatement cs = null;
		ResultSet rs = null;
		try {
			loadDriver();
			conn = getConn();
			String sql = "call order_manage.get_client_status(?)";
			cs = conn.prepareCall(sql);
			cs.setInt(1, id);
			rs = cs.executeQuery();
			if (rs == null) {
				return null;
			}
			while (rs.next()) {
				String status = rs.getString("name");
				ClientStatus cStatus = ClientStatus.valueOf(status.toUpperCase());
				return cStatus;
			}

		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
				cs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}

	public int getClientStatusId(ClientStatus cStatus) {
		Connection conn = null;
		CallableStatement cs = null;
		ResultSet rs = null;
		try {
			loadDriver();
			conn = getConn();
			String sql = "call order_manage.get_client_statusId(?)";
			cs = conn.prepareCall(sql);

			cs.setString(1, String.valueOf(cStatus));

			rs = cs.executeQuery();
			if (rs == null) {
				return -1;
			}
			while (rs.next()) {
				int id = rs.getInt("id");
				return id;
			}

		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
				cs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return -1;
	}

	public int getClientTypeId(ClientType cType) {
		Connection conn = null;
		CallableStatement cs = null;
		ResultSet rs = null;
		try {
			loadDriver();
			conn = getConn();
			String sql = "call order_manage.get_client_TypeId(?)";
			cs = conn.prepareCall(sql);

			cs.setString(1, String.valueOf(cType));
			rs = cs.executeQuery();
			if (rs == null) {
				return -1;
			}

			while (rs.next()) {
				int id = rs.getInt("id");
				return id;
			}

		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
				cs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return -1;
	}

	public void loadAccount() {
		Client client = OrderApp.getInstance().getUser().getClient();
		Account account = client.getAccount();
		Transaction transaction = null;
		Connection conn = null;
		CallableStatement cs = null;
		ResultSet rs = null;
		try {
			loadDriver();
			conn = getConn();
			String sql = "call order_manage.get_transaction_from_account(?)";
			cs = conn.prepareCall(sql);
			cs.setInt(1, account.getId());
			rs = cs.executeQuery();
			while (rs.next()) {
				int id = rs.getInt("id");
				double value = rs.getDouble("value");
				String time = rs.getString("create_datetime");
				TransacationType type = getTransacationType(rs.getInt("transaction_type_id"));
				transaction = new Transaction(id, value, time, account, type);
				account.getTransaction().add(transaction);
			}
		} catch (ClassNotFoundException | SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
				cs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	//把一个client下的所有order获取
	public void getOrders() {
		Client client = OrderApp.getInstance().getUser().getClient();
		Connection conn = null;
		CallableStatement cs = null;
		ResultSet rs = null;

		try {
			loadDriver();
			conn = getConn();
			String sql = "call get_orders_from_client(?)";
			cs = conn.prepareCall(sql);
			cs.setInt(1, client.getId());
			rs = cs.executeQuery();
			while (rs.next()) {
				int orderId = rs.getInt("id");
				String time = rs.getString("create_datetime");
				String memo = rs.getString("memo");
				OrderStatus orderStatus = OrderStatus.values()[rs.getInt("order_status_id") - 1];
				int paymentId = rs.getInt("payment_id");
				Payment payment = getPayment(paymentId);
				ArrayList<OrderItem> orderItem = getOrderItems(orderId);
				Order order = new Order(orderId, time, memo, orderStatus, payment, client, orderItem);
				for (OrderItem item : orderItem) {
					order.setMoney(order.getMoney() + item.getMoney());
				}

				client.getOrders().add(order);

			}

		} catch (ClassNotFoundException | SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
				cs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

	public ArrayList<OrderItem> getOrderItems(int orderId) {
		Connection conn = null;
		CallableStatement cs = null;
		ResultSet rs = null;
		ArrayList<OrderItem> items = new ArrayList<>();
		try {
			loadDriver();
			conn = getConn();
			String sql = "call get_order_item(?)";
			cs = conn.prepareCall(sql);
			cs.setInt(1, orderId);

			rs = cs.executeQuery();

			while (rs.next()) {
				int id = rs.getInt("id");
				int amount = rs.getInt("amount");
				String memo = rs.getString("memo");
				Product product = getProduct(rs.getInt("product_id"));
				double money = rs.getDouble("money");
				items.add(new OrderItem(id, amount, memo, product, money));
			}
			return items;

		} catch (ClassNotFoundException | SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
				cs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return null;
	}

	public Product getProduct(int id) {
		Connection conn = null;
		CallableStatement cs = null;
		ResultSet rs = null;
		try {
			loadDriver();
			conn = getConn();
			String sql = "call get_product(?)";
			cs = conn.prepareCall(sql);
			cs.setInt(1, id);
			rs = cs.executeQuery();
			while (rs.next()) {
				String code = rs.getString("code");
				String name = rs.getString("name");
				double price = rs.getDouble("price");
				ProductUnit unit = ProductUnit.values()[rs.getInt("product_unit_id") - 1];
				Catalog catalog = Catalog.values()[rs.getInt("catalog_id") - 1];
				return new Product(id, code, name, price, unit, catalog);

			}

		} catch (ClassNotFoundException | SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				conn.close();
				cs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}

	public Payment getPayment(int paymentId) {
		Connection conn = null;
		CallableStatement cs = null;
		ResultSet rs = null;

		try {
			loadDriver();
			conn = getConn();
			String statusql = "call get_payment(?)";
			cs = conn.prepareCall(statusql);
			cs.setInt(1, paymentId);
			rs = cs.executeQuery();
			while (rs.next()) {
				int statusId = rs.getInt("payment_status_id");
				int methodId = rs.getInt("payment_method_id");
				return new Payment(paymentId, PaymentStatus.values()[statusId - 1],
						PaymentMethod.values()[methodId - 1]);
			}

		} catch (ClassNotFoundException | SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				conn.close();
				cs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}

	public TransacationType getTransacationType(int id) {
		Connection conn = null;
		CallableStatement cs = null;
		ResultSet rs = null;

		try {
			loadDriver();
			conn = getConn();
			String sql = "call get_transaction_type(?)";
			cs = conn.prepareCall(sql);
			cs.setInt(1, id);
			rs = cs.executeQuery();
			while (rs.next()) {
				String type = rs.getString("name");
				return TransacationType.valueOf(type);
			}

		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			try {
				conn.close();
				cs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		return null;
	}
	
	public Order createOrder(Order order){
		Connection conn = null;
		CallableStatement cs = null;
		ResultSet rs = null;
		
		try {
			loadDriver();
			conn = getConn();
			String sql = "call insert_order(?,?,?,?,?,?)";
			cs = conn.prepareCall(sql);
			cs.setString(1, order.getCreateDatetime());
			cs.setString(2, order.getMemo());
			cs.setInt(3, 2);
			cs.setInt(4, createPayment(order.getPayment()).getId());
			cs.setInt(5, order.getClient().getId());
			cs.setDouble(6, order.getMoney());
			rs = cs.executeQuery();
			while(rs.next()){
				int id = rs.getInt("id");				
				order.setId(id);
				return order;
			}
			
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				conn.close();
				cs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}
	
	public Payment createPayment(Payment payment){
		Connection conn = null;
		CallableStatement cs = null;
		ResultSet rs = null;
		try {
			loadDriver();
			conn = getConn();
			String sql = "call insert_payment(?,?)";
			cs = conn.prepareCall(sql);
			cs.setInt(1, 1);
			cs.setInt(2, 1);
			rs=cs.executeQuery();
			while(rs.next()){
				int id = rs.getInt("id");
				payment.setId(id);				
				return payment;
			}
			
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				conn.close();
				cs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
		
		
	}
	public List<Product> getAllProducts(){
		Connection conn = null;
		CallableStatement cs = null;
		ResultSet rs = null;
		List<Product> products = new ArrayList<>();
		try {
			loadDriver();
			conn = getConn();
			String sql = "call get_allproducts()";
			cs = conn.prepareCall(sql);
			rs=cs.executeQuery();
			
			while(rs.next()){
				int id = rs.getInt("id");
				String code = rs.getString("code");
				String name = rs.getString("name");
				double price = rs.getDouble("price");
				ProductUnit unit = ProductUnit.values()[rs.getInt("product_unit_id")-1];
				Catalog catalog = Catalog.values()[rs.getInt("catalog_id")-1];
				products.add(new Product(id, code, name, price, unit, catalog));	
				
			
			}
			return products;
		} catch (ClassNotFoundException | SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				conn.close();
				cs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return null;
	}
	
	public void updateOrder(Order order){
		Connection conn = null;
		CallableStatement cs = null;
		try {
			loadDriver();
			conn = getConn();
			String sql = "call update_order(?,?,?,?,?)";
			cs = conn.prepareCall(sql);
			cs.setInt(1, order.getId());
			cs.setString(2, order.getMemo());
			
			int index = 0;
			int i = 0;
			for(OrderStatus orderStatus : OrderStatus.values()){
				if(order.getStatus().equals(orderStatus)){
					index = i;
				}
				i++;
			}
			cs.setInt(3, index);
			
			i=0;
			for(OrderStatus orderStatus : OrderStatus.values()){
				if(order.getStatus().equals(orderStatus)){
					index = i;
				}
				i++;
			}
			cs.setInt(4, index);
			
			cs.setDouble(5, order.getMoney());
			cs.execute();
			
		} catch (ClassNotFoundException | SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				conn.close();
				cs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
 		
  	}
 	
	public void updateOrderItem(OrderItem orderItem,Order order){
		Connection conn = null;
		CallableStatement cs = null;
		try {
			loadDriver();
			conn = getConn();
			String sql = "call update_order_item(?,?,?,?,?,?)";
			cs = conn.prepareCall(sql);
			cs.setInt(1, orderItem.getId());
			cs.setInt(2, orderItem.getAmount());
			cs.setString(3, orderItem.getMemo());
			cs.setInt(4, orderItem.getProduct().getId());
			cs.setInt(5, order.getId());
			cs.setDouble(6, orderItem.getMoney());	
			cs.execute();
			
		} catch (ClassNotFoundException | SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				conn.close();
				cs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		
	}
	
	public void updateAccount(Account account){
		Connection conn = null;
		CallableStatement cs = null;
		try {
			loadDriver();
			conn = getConn();
			String sql = "call update_account(?,?)";
			cs = conn.prepareCall(sql);
			cs.setInt(1, account.getId());
			cs.setDouble(2, account.getBalance());
	
			cs.execute();
			
		} catch (ClassNotFoundException | SQLException e) {
			e.printStackTrace();
		}finally {
			try {
				conn.close();
				cs.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}
	
}
